1. Field of the Invention
The present invention relates generally to the field of database systems and, more particularly, to a system and method allowing for transparent updates of partitioned views in database environments.
2. Brief Description of Prior Developments
Database systems store, retrieve, and process information. In order to retrieve information from the database, a user provides a query (written in a query language such as SQL), where the query specifies the information to be retrieved and the manner in which it is to be manipulated or evaluated in order to provide a useful result. To process the query, the database system converts the query into a relational expression that describes algebraically the result specified by the query. The relational expression is then used to produce an execution plan, which describes particular steps to be taken by a computer in order to produce the sought result.
Database environments comprise various configurations having cooperating homogeneous and/or heterogeneous data stores. These data stores generally maintain data in tables (T) that may reside locally on a single data store or be may distributed among several data stores in a distributed database environment. Included in the category of distributed database environments are clustered databases and federated databases. A clustered database configuration contemplates several data stores closely cooperating to produce a singular view of the data stored on the data stores. Comparatively, a database federation is a group of data stores (e.g. computer servers) that are administered independently, but which cooperate to share the processing load of a system. These various database environments support a number of views that may be imposed to better manage the underlying table (T) data. For example, these database environments (e.g. stand-alone, clustered, and federated) may be partitioned such that the table or tables storing data may be more efficiently handled. In the case of distributed database environments, distributed partitioned views may be created.
Generally, a partitioned view joins horizontally partitioned data from a set of member tables across one or more data stores making the data as if it appears from one database table. In a local partitioned view, all participating tables and the view reside on the same instance of the data store (e.g. the same instance of SQL server). In a distributed partitioned view, at least one of the participating tables resides on a different (i.e. remote) data store. For partitioned tables to exist the location of single data row in a partitioned table (T) must be uniquely located. Current implementations satisfy this requirement through the use of ranges (e.g. column and/or row ranges), or, through hashing functions which operate on columns and/or rows. In the ranges-based implementation, the member tables of the partitioned view are designed so that each table stores a horizontal slice of the original table based on a range of key values. The ranges are based on the data values in a partitioning column. The partitioning column serves to contain values that facilitate the creation and maintenance of partitioned views in partitioned database environments.
The member tables of the partitioned view are designed so that each table stores a horizontal slice of the original table based on a range of key values. The ranges are based on the data values in a partitioning column. The range of values in each member table is enforced by a database environment constraint (e.g. CHECK constraint) on the partitioning column, such that ranges do not overlap.
Current practices allow for the update of database environment views such that data represented by such views can be modified, deleted, or replaced. In the context of updating partitioned views in distributed database environments, such practices lend to inefficient and non-optimal uses of processing resources. Stated differently, tables residing on a number of data stores may require numerous processing steps to satisfy offered update requests. For example, if data is to be updated on remote participating data stores of partitioned database environments, a number of unnecessary steps, such as, reading from and writing to numerous tables may be performed.
In view of the foregoing, it is appreciated that there exists a need for an improved query execution strategy that overcomes the drawbacks of existing practices.
Generally the present invention provides a system and methods that allow for the efficient transparent update of views in database environments. In an illustrative implementation, a plurality of data stores are provided such that they are arranged in a data store federation having distributed partitioned views. In operation, the partitioned view may be modified by the inserting new data, deleting existing data, and updating existing data. A partitioning function operates on data in the database environment to provide updates of partitioned views. In the contemplated implementation, the partitioning function is generated for each partitioned view. The partitioning function is generated by loading the partitioning constraint expression on the partitioning column for each child table of the partitioned view. The tables are remapped such that a view column is superimposed on the partitioning column. The resulting remapped table is stored in an array of expressions. These expressions are then processed to produce the partitioning function. The partition-id of each table in the partitioned view is set based on their order of appearance in the initial view definition. Further, the present invention contemplates partitioned column updates and non-partitioned column updates.
Further aspects of the invention are described below.